#!/bin/bash

# 拉链表更新

source /etc/profile

if [ -n "$1" ]
 then
   do_date=$1
 else
   do_date=`date -d "-1 day" +%F`
fi

sql="
insert overwrite table dim.dim_shops
   select shopid, userid, areaid, shopname,
          shoplevel, status, createtime, modifytime,
          CASE WHEN modifytime is not null THEN substr(modifytime,0,10)
               ELSE  substr(createtime,0,10) END AS startdate,
          '9999-12-31' AS enddate
          from ods.ods_shops where dt = '$do_date'
   union all
   select b.shopid, b.userid ,b.areaid , b.shopname, b.shoplevel,
          b.status, b.createtime, b.modifytime, b.startdate,
          CASE WHEN a.shopid is not null and b.enddate ='9999-12-31' 
               THEN date_add('$do_date',-1) 
               ELSE b.enddate end as enddate
          from (select * from ods.ods_shops where dt='$do_date') a
          right join dim.dim_shops b on a.shopid = b.shopid;
"
 
hive -e "$sql"
